﻿Public Class clsDBMSTRandCheck

    ''' <summary>
    ''' 工程用のルール検索
    ''' </summary>
    ''' <param name="ProcCD">工程</param>
    ''' <param name="ProdName">品名</param>
    ''' <param name="PKG">PKG</param>
    ''' <param name="carflg">車品区分</param>
    ''' <param name="EquipNo">設備No.</param>
    ''' <returns></returns>
    Public Function selectRuleForRC(ProcCD As String, ProdName As String, PKG As String, carflg As String, EquipNo As String) As DataTable
        Dim szSql As String = ""

        szSql &= "SELECT *  "
        szSql &= " FROM PER.T_MST_RANDCHECK "
        szSql &= " WHERE PROCCD='" & ProcCD & "' AND "
        szSql &= " CARFLG ='" & carflg & "' AND "
        If ProdName <> "" Then
            szSql &= " PRODNAME ='" & ProdName & "' AND "
        End If
        If PKG <> "" Then
            szSql &= " PKG LIKE ('%" & PKG & "%') AND "
        End If
        If EquipNo <> "" Then
            szSql &= " EquipNo ='" & EquipNo & "' AND "
        End If
        szSql &= " 1 = 1 "
        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)

        Return dt
    End Function
    Public Function selectRule(ProcCD As String, carflg As String) As DataTable
        Dim szSql As String = ""

        szSql &= "SELECT "
        szSql &= "  SYS_ID"
        szSql &= "  , PROCCD"
        szSql &= "  , CASE "
        szSql &= "    WHEN RULENAME='0' THEN '品种别' "
        szSql &= " WHEN RULENAME='1' THEN 'PKG别' "
        szSql &= " WHEN RULENAME='2' THEN '' "
        szSql &= " END RULENAME"
        szSql &= "  , PRODNAME"
        szSql &= "  , PKG"
        szSql &= "  , CASE  "
        szSql &= "    WHEN CHECKTYPE='0' THEN '料盒数' "
        szSql &= " WHEN CHECKTYPE='1' THEN 'LOT数' "
        szSql &= " WHEN CHECKTYPE='2' THEN '' "
        szSql &= " END CHECKTYPE"
        szSql &= "  , CHECKVALUE"
        szSql &= "  , EQUIPNO"
        szSql &= "  , CASE "
        szSql &= "  WHEN CHECKFREQ='0' THEN '正常'"
        szSql &= "	WHEN CHECKFREQ='1' THEN '放宽'"
        szSql &= "	END CHECKFREQ"
        szSql &= "  , CHECKNUM"
        szSql &= "  , CARFLG "
        szSql &= "  , NGNUM "
        szSql &= " FROM PER.T_MST_RANDCHECK "
        szSql &= " WHERE PROCCD='" & ProcCD & "' AND "
        szSql &= " CARFLG ='" & carflg & "' "
        szSql &= " ORDER BY  SYS_ID "


        Dim dt As DataTable = DBacs.ExecuteSql(szSql, -1)

        Return dt
    End Function
    Public Sub insertRule(ProcCD As String,
                RuleName As String,
                ProdName As String,
                PKG As String,
                CheckType As String,
                CheckValue As Integer,
                EquipNo As String,
                CheckFreq As String,
                CheckNum As Integer,
                CarFlg As String,
                NGNum As Integer
                )
        Dim szSql As String = ""
        szSql &= "INSERT INTO PER.T_MST_RANDCHECK ("
        szSql &= "PROCCD "
        szSql &= ",RuleNAME "
        szSql &= ",ProdName "
        szSql &= ",pkg "
        szSql &= ",CheckType "
        szSql &= ",CheckValue "
        szSql &= ",EquipNo "
        szSql &= ",CheckFreq "
        szSql &= ",CheckNum "
        szSql &= ",CarFlg "
        szSql &= ",NGNum "
        szSql &= ") VALUES (?,?,?,?,?,?,?,?,?,?,?)"
        RuleName = setRuleValue(RuleName)
        CheckType = setTypeValue(CheckType)
        CheckFreq = setFreqValue(CheckFreq)
        DBacs.ExecuteUpdateSql(szSql, -1, ProcCD, RuleName, ProdName, PKG, CheckType, CheckValue, EquipNo, CheckFreq, CheckNum, CarFlg, NGNum)

    End Sub
    Public Sub delRule(sys_id As String)
        Dim szSql As String = ""

        szSql &= "DELETE FROM  PER.T_MST_RANDCHECK "
        szSql &= "WHERE  SYS_ID = ?"

        DBacs.ExecuteUpdateSql(szSql, -1, sys_id)
    End Sub
    Public Sub updateRule(sys_id As String,
                        ProcCD As String,
                        RuleName As String,
                        ProdName As String,
                        PKG As String,
                        CheckType As String,
                        CheckValue As Integer,
                        EquipNo As String,
                        CheckFreq As String,
                        CheckNum As Integer,
                        CarFlg As String,
                        NGNum As Integer
                        )
        Dim szSql As String = ""

        szSql &= "UPDATE PER.T_MST_RANDCHECK "
        szSql &= "SET  PROCCD = ?"
        szSql &= "  , RuleNAME = ?"
        szSql &= "  , ProdName = ?"
        szSql &= "  , pkg = ?"
        szSql &= "  , CheckType = ?"
        szSql &= "  , CheckValue = ?"
        szSql &= "  , EquipNo = ?"
        szSql &= "  , CheckFreq = ?"
        szSql &= "  , CheckNum = ?"
        szSql &= "  , CarFlg = ? "
        szSql &= "  , NGNum = ? "
        szSql &= "WHERE  SYS_ID = ? "

        RuleName = setRuleValue(RuleName)
        CheckType = setTypeValue(CheckType)
        CheckFreq = setFreqValue(CheckFreq)
        DBacs.ExecuteUpdateSql(szSql, -1, ProcCD, RuleName, ProdName, PKG, CheckType, CheckValue, EquipNo, CheckFreq, CheckNum, CarFlg, NGNum, sys_id)
    End Sub
    Function DBNullTOblank(obj As Object) As String
        DBNullTOblank = IIf(IsDBNull(obj), "", obj)
    End Function
    Function DBNullTOZero(obj As Object) As Integer
        DBNullTOZero = IIf(IsDBNull(obj), 0, obj)
    End Function
    Public Function updateMSTData(ByRef dtCivil As DataTable, ByRef dtCar As DataTable) As Boolean
        DBacs.BeginTransaction()
        Try
            If IsNothing(dtCivil) = False AndAlso IsNothing(dtCivil.GetChanges) = False Then

                For Each drClivil As DataRow In dtCivil.GetChanges.Rows
                    If drClivil.RowState = DataRowState.Added Then
                        insertRule(
                        DBNullTOblank(drClivil("ProcCD")),
                        DBNullTOblank(drClivil("RuleName")),
                        DBNullTOblank(drClivil("ProdName")),
                        DBNullTOblank(drClivil("PKG")),
                        DBNullTOblank(drClivil("CheckType")),
                        DBNullTOZero(drClivil("CheckValue")),
                        DBNullTOblank(drClivil("EquipNo")),
                        DBNullTOblank(drClivil("CheckFreq")),
                        DBNullTOZero(drClivil("CheckNum")),
                        DBNullTOblank(drClivil("CarFlg")),
                        DBNullTOZero(drClivil("NGNum")))
                    End If
                    If drClivil.RowState = DataRowState.Modified Then
                        updateRule(
                        DBNullTOblank(drClivil("SYS_ID")),
                        DBNullTOblank(drClivil("ProcCD")),
                        DBNullTOblank(drClivil("RuleName")),
                        DBNullTOblank(drClivil("ProdName")),
                        DBNullTOblank(drClivil("PKG")),
                        DBNullTOblank(drClivil("CheckType")),
                        DBNullTOZero(drClivil("CheckValue")),
                        DBNullTOblank(drClivil("EquipNo")),
                        DBNullTOblank(drClivil("CheckFreq")),
                        DBNullTOZero(drClivil("CheckNum")),
                        DBNullTOblank(drClivil("CarFlg")),
                        DBNullTOZero(drClivil("NGNum")))
                    End If
                    If drClivil.RowState = DataRowState.Deleted Then
                        delRule(DBNullTOblank(drClivil("SYS_ID", DataRowVersion.Original)))
                    End If
                Next
            End If
            If IsNothing(dtCar) = False AndAlso IsNothing(dtCar.GetChanges) = False Then
                For Each drCar As DataRow In dtCar.GetChanges.Rows
                    If drCar.RowState = DataRowState.Added Then
                        insertRule(
                        DBNullTOblank(drCar("ProcCD")),
                        DBNullTOblank(drCar("RuleName")),
                        DBNullTOblank(drCar("ProdName")),
                        DBNullTOblank(drCar("PKG")),
                        DBNullTOblank(drCar("CheckType")),
                        DBNullTOZero(drCar("CheckValue")),
                        DBNullTOblank(drCar("EquipNo")),
                        DBNullTOblank(drCar("CheckFreq")),
                        DBNullTOZero(drCar("CheckNum")),
                        DBNullTOblank(drCar("CarFlg")),
                        DBNullTOZero(drCar("NGNum")))
                    End If
                    If drCar.RowState = DataRowState.Modified Then
                        updateRule(
                        DBNullTOblank(drCar("SYS_ID")),
                        DBNullTOblank(drCar("ProcCD")),
                        DBNullTOblank(drCar("RuleName")),
                        DBNullTOblank(drCar("ProdName")),
                        DBNullTOblank(drCar("PKG")),
                        DBNullTOblank(drCar("CheckType")),
                        DBNullTOZero(drCar("CheckValue")),
                        DBNullTOblank(drCar("EquipNo")),
                        DBNullTOblank(drCar("CheckFreq")),
                        DBNullTOZero(drCar("CheckNum")),
                        DBNullTOblank(drCar("CarFlg")),
                        DBNullTOZero(drCar("NGNum")))
                    End If
                    If drCar.RowState = DataRowState.Deleted Then
                        delRule(DBNullTOblank(drCar("SYS_ID", DataRowVersion.Original)))
                    End If
                Next
            End If
            DBacs.CommitTransaction()
            Return True
        Catch ex As Exception
            DBacs.RollbackTransaction()
            Return False

        End Try
    End Function
    Private Function setRuleValue(name As String) As String
        Dim value As String = name
        If name = "品种别" Then
            value = "0"
        ElseIf name = "PKG别" Then
            value = "1"
        Else
            value = "2"
        End If
        Return value
    End Function
    Private Function setTypeValue(name As String) As String
        Dim value As String = name
        If name = "料盒数" Then
            value = "0"
        ElseIf name = "LOT数" Then
            value = "1"
        Else
            value = "2"
        End If
        Return value
    End Function
    Private Function setFreqValue(name As String) As String
        Dim value As String = name
        If name = "正常" Then
            value = "0"
        ElseIf name = "放宽" Then
            value = "1"
        Else
            value = "2"
        End If
        Return value
    End Function

End Class
